package com.logisticsmanagement.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

/*
 * database util,help us to construct a sql string and insert parameter into preparedStatement if this value is not null
 * we resume that you attribute name is same with column name in database and class name is same with table in database
 * and then ,all of you domain attribute type is reference type,do not base type such as int,boolean,long
 * @author dengweijia
 */
public class DatabaseUtil {

	public static PreparedStatement  getSelectPageStatment(Object domain,Connection conn, int pageNow,int pageSize,String Orderby){
		
		StringBuilder sql =new StringBuilder("select * from ");
		PreparedStatement statement =null;
		if(pageNow < 1)
			pageNow = 1;
		if(pageSize < 1)
			pageSize = 10;
		int i = 0;
		boolean andTag=false;
		boolean whereTag = true;
		
		try {
			Class clazz = domain.getClass();
			String completeName = clazz.getName();
			int dotindex = completeName.lastIndexOf(".");
			sql.append(completeName.substring(dotindex+1));
			
			Field[] fields = clazz.getDeclaredFields();
			
			for(Field field:fields){
				Object value;
				
				String fieldName = field.getName();
				String MethodName ="get"+(fieldName.charAt(0)+"").toUpperCase()+fieldName.substring(1);
				
				Method method = clazz.getDeclaredMethod(MethodName);
				value = method.invoke(domain);
				
				if(value == null){
					continue;
				}
				
				if(whereTag)
				{
					sql.append(" where ");
					whereTag = false;
				}
				
				if(andTag){
					sql.append("and ");
				}
				else{
					andTag =true;
				}
				
				sql.append(fieldName);
				sql.append("=? ");
			}
			
			
			if(Orderby != null){
				sql.append("order by ");
				sql.append(Orderby);
				sql.append(" asc ");
			}
			
			sql.append("limite ");
			sql.append(String.valueOf((pageNow-1)*pageSize));
			sql.append(",");
			sql.append(String.valueOf(pageSize));
			
			statement = conn.prepareStatement(sql.toString());
			System.out.println("auto fill sql: "+ sql.toString());
			
			for(Field field:fields){
				Object value;
				
				String fieldName = field.getName();
				String MethodName ="get"+(fieldName.charAt(0)+"").toUpperCase()+fieldName.substring(1);
				
				Method method = clazz.getDeclaredMethod(MethodName);
				value = method.invoke(domain);
				
				if(value == null){
					continue;
				}
				i++;
				String type = field.getType().getName();
				if(type.equals("java.lang.String")){
					statement.setString(i, (String)value);
				}else if(type.equals("java.lang.Integer")){
					statement.setInt(i, (Integer)value);
				}
				else if(type.equals("int")){
					statement.setInt(i, (Integer)value);
				}
				else if(type.equals("java.util.Date")){
					statement.setDate(i, new java.sql.Date(((Date)value).getTime()));
				}else if(type.equals("java.lang.Double")){
					statement.setDouble(i, (Double)value);
				}else if(type.equals("double")){
					statement.setDouble(i, (Double)value);
				}
				else if(type.equals("java.lang.Boolean")){
					statement.setBoolean(i, (Boolean)value);
				}else if(type.equals("boolean")){
					statement.setBoolean(i, (Boolean)value);
				}
				else if(type.equals("java.lang.Long")){
					statement.setLong(i, (Long)value);
				}
				else if(type.equals("java.lang.Long")){
					statement.setLong(i, (Long)value);
				}else {
					throw new RuntimeException("the parameter type "+ type+" is not implement");
				}
			}
			
			
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
	
	public static String getSelectPageSql(Object domain, int pageNow,int pageSize,String Orderby){
	
		StringBuilder sql =new StringBuilder("select * from ");
		if(pageNow < 1)
			pageNow = 1;
		if(pageSize < 1)
			pageSize = 10;
		int i = 0;
		boolean andTag=false;
		boolean whereTag = true;
		
			Class clazz = domain.getClass();
			String completeName = clazz.getName();
			int dotindex = completeName.lastIndexOf(".");
			sql.append(completeName.substring(dotindex+1));
			
			Field[] fields = clazz.getDeclaredFields();
			
			try {
				for(Field field:fields){
					Object value;
					
					String fieldName = field.getName();
					String MethodName ="get"+(fieldName.charAt(0)+"").toUpperCase()+fieldName.substring(1);
					
					Method method = clazz.getDeclaredMethod(MethodName);
					value = method.invoke(domain);
					
					if(value == null){
						continue;
					}
					
					if(whereTag)
					{
						sql.append(" where ");
						whereTag = false;
					}
					
					if(andTag){
						sql.append("and ");
					}
					else{
						andTag =true;
					}
					
					sql.append(fieldName);
					String type = field.getType().getName();
					sql.append("=");
					
					if(type.equals("java.lang.String")){
						sql.append("'");
					}
					sql.append(value.toString());
					if(type.equals("java.lang.String")){
						sql.append("'");
					}
					sql.append(" ");
				}
				
				
				if(Orderby != null){
					sql.append("order by ");
					sql.append(Orderby);
					sql.append(" asc ");
				}
				
				sql.append("limite ");
				sql.append(String.valueOf((pageNow-1)*pageSize));
				sql.append(",");
				sql.append(String.valueOf(pageSize));
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return sql.toString();
	}
	
	public static PreparedStatement getSelcectCountStatement(Object domain,Connection conn){
		
		

		StringBuilder sql =new StringBuilder("select count(*) from ");
		PreparedStatement statement =null;
		int i = 0;
		boolean andTag=false;
		boolean whereTag = true;
		
		try {
			Class clazz = domain.getClass();
			String completeName = clazz.getName();
			int dotindex = completeName.lastIndexOf(".");
			sql.append(completeName.substring(dotindex+1));
			
			Field[] fields = clazz.getDeclaredFields();
			
			for(Field field:fields){
				Object value;
				
				String fieldName = field.getName();
				String MethodName ="get"+(fieldName.charAt(0)+"").toUpperCase()+fieldName.substring(1);
				
				Method method = clazz.getDeclaredMethod(MethodName);
				value = method.invoke(domain);
				
				if(value == null){
					continue;
				}
				
				if(whereTag)
				{
					sql.append(" where ");
					whereTag = false;
				}
				
				if(andTag){
					sql.append("and ");
				}
				else{
					andTag =true;
				}
				
				sql.append(fieldName);
				sql.append("=? ");
			}
			statement = conn.prepareStatement(sql.toString());
			System.out.println("auto fill sql: "+ sql.toString());
			
			for(Field field:fields){
				Object value;
				
				String fieldName = field.getName();
				String MethodName ="get"+(fieldName.charAt(0)+"").toUpperCase()+fieldName.substring(1);
				
				Method method = clazz.getDeclaredMethod(MethodName);
				value = method.invoke(domain);
				
				if(value == null){
					continue;
				}
				i++;
				String type = field.getType().getName();
				if(type.equals("java.lang.String")){
					statement.setString(i, (String)value);
				}else if(type.equals("java.lang.Integer")){
					statement.setInt(i, (Integer)value);
				}
				else if(type.equals("int")){
					statement.setInt(i, (Integer)value);
				}
				else if(type.equals("java.util.Date")){
					statement.setDate(i, new java.sql.Date(((Date)value).getTime()));
				}else if(type.equals("java.lang.Double")){
					statement.setDouble(i, (Double)value);
				}else if(type.equals("double")){
					statement.setDouble(i, (Double)value);
				}
				else if(type.equals("java.lang.Boolean")){
					statement.setBoolean(i, (Boolean)value);
				}else if(type.equals("boolean")){
					statement.setBoolean(i, (Boolean)value);
				}
				else if(type.equals("java.lang.Long")){
					statement.setLong(i, (Long)value);
				}
				else if(type.equals("java.lang.Long")){
					statement.setLong(i, (Long)value);
				}else {
					throw new RuntimeException("the parameter type "+ type+" is not implement");
				}
			}
			
			
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return statement;
	}
}
